﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SyncGeographyTiers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SyncGeographyTiers]
GO
CREATE PROCEDURE [dbo].[sp_SyncGeographyTiers] (
	@CultureInfo [nvarchar](20),
	@Stamp DATETIME=NULL,
	@Count INT=0
)
AS
BEGIN
	DECLARE @sqlExp AS NVARCHAR(MAX);
	DECLARE @whereExp AS NVARCHAR(MAX);

	IF(@Count<1)
		SET @Count=100

	SET @sqlExp = 'SELECT TOP ('+CONVERT(NVARCHAR(MAX),@Count)+') [Key],[ParentKey],[TierValue],[Name],[PostCode],[TelCode],[CountryKey],[CultureInfo],[LastUpdatedStamp] FROM [dbo].[view_GeographyTier]';
	SET @whereExp = '';

	IF(@Stamp IS NOT NULL)
		SET @whereExp = @whereExp + ' [LastUpdatedStamp]>''' + @Stamp + ''' AND ';

	IF(@CultureInfo IS NOT NULL)
		SET @whereExp = @whereExp + ' [CultureInfo]=''' + @CultureInfo + ''' AND ';

	IF(@whereExp <> '')
	BEGIN
		SET @whereExp = SUBSTRING(@whereExp, 0, LEN(@whereExp) - 3);
		SET @sqlExp = @sqlExp + ' WHERE ' + @whereExp +' ORDER BY [LastUpdatedStamp]';
	END

	EXEC sp_executesql @sqlExp;

END

GO


